package com.william.jdbcplus.boot.test.dao.impl;

import com.william.jdbcplus.boot.starter.util.DaoUtil;
import com.william.jdbcplus.boot.starter.util.DbUtil;
import com.william.jdbcplus.boot.starter.wrap.QueryBootWrap;
import com.william.jdbcplus.boot.starter.wrap.QueryLambdaBootWrap;
import com.william.jdbcplus.boot.test.dao.SysUserDao;
import com.william.jdbcplus.boot.test.entity.*;
import com.william.jdbcplus.boot.test.vo.SysUserDepVo;
import com.william.jdbcplus.boot.test.vo.SysUserSysDepartVO;
import com.william.jdbcplus.common.page.IPage;
import com.william.jdbcplus.common.page.Page;
import com.william.jdbcplus.core.condition.wrap.IWrap;
import com.william.jdbcplus.core.query.EntityWrap;
import com.william.jdbcplus.core.toolkit.StringPool;
import com.william.jdbcplus.core.toolkit.StringUtil;
import com.william.jdbcplus.core.util.JdbcDaoUtil;
import lombok.AllArgsConstructor;
import org.springframework.stereotype.Repository;

import java.util.Arrays;
import java.util.List;

@Repository
@AllArgsConstructor
public class SysUserDaoImpl implements SysUserDao {
    /**
     * 通过用户账号查询用户信息
     *
     * @param username
     * @return
     */
    @Override
    public SysUser getUserByName(String username) {
        // select * from  sys_user  where username = #{username} and del_flag = 0
        QueryLambdaBootWrap bootstrap = new QueryLambdaBootWrap();
        bootstrap
                .selectAll(SysUser.class)
                .from(SysUser.class)
                .eq(SysUser::getDelFlag, 0)
                .eq(SysUser::getUsername, username);

        SysUser sysUser = bootstrap.queryOne(SysUser.class);
        return sysUser;
    }

    /**
     * 根据部门Id查询用户信息
     *
     * @param page
     * @param departId
     * @param username
     * @return
     */
    @Override
    public IPage<SysUser> getUserByDepId(Page page, String departId, String username) {
        //        select * from sys_user where del_flag = 0 and id in (select user_id from sys_user_depart where dep_id=#{departId})
        //		<if test="username!=null and username!=''">
        //                and username = #{username}
        //		</if>
        QueryLambdaBootWrap bootstrap = new QueryLambdaBootWrap();
        bootstrap
                .selectAll(SysUser.class)
                .from(SysUser.class)
                .eq(SysUser::getDelFlag, 0)
                .in(SysUser::getId, i -> i
                        .select(SysUserDepart::getUserId)
                        .from(SysUserDepart.class)
                        .eq(SysUserDepart::getDepId, departId)
                );

        if (StringUtil.isNotBlank(username)) {
            bootstrap.eq(SysUser::getUsername, username);
        }

        Page<SysUser> result = bootstrap.queryPage(page, SysUser.class);
        return result;
    }

    /**
     * 根据用户Ids,查询用户所属部门名称信息
     *
     * @param userIds
     * @return
     */
    @Override
    public List<SysUserDepVo> getDepNamesByUserIds(List<String> userIds) {
        //        select d.depart_name,ud.user_id from sys_user_depart ud,sys_depart d where d.id = ud.dep_id and ud.user_id in
        //		<foreach collection="userIds" index="index" item="id" open="(" separator="," close=")">
        //			#{id}
        //		</foreach>

        QueryLambdaBootWrap bootstrap = new QueryLambdaBootWrap();
        bootstrap
                .select(SysDepart::getDepartName)
                .select(SysUserDepart::getUserId)
                .from(SysUserDepart.class)
                .from(SysDepart.class)
                .on(SysDepart::getId, SysUserDepart::getDepId)
                .in(SysUserDepart::getUserId, userIds);

        List<SysUserDepVo> list = bootstrap.queryList(SysUserDepVo.class);
        return list;
    }

    /**
     * 根据部门Ids,查询部门下用户信息
     *
     * @param page
     * @param departIds
     * @param username
     * @return
     */
    @Override
    public IPage<SysUser> getUserByDepIds(Page page, List<String> departIds, String username) {
        //        select * from sys_user where del_flag = 0
        //                <if test="departIds!=null  and departIds.size()>0">
        //                and id in (select user_id from sys_user_depart where dep_id in
        //			<foreach collection="departIds" index="index" item="id" open="(" separator="," close=")">
        //				#{id}
        //			</foreach>
        //			)
        //		</if>
        //		<if test="username!=null and username!=''">
        //                and username = #{username}
        //		</if>
        QueryLambdaBootWrap bootstrap = new QueryLambdaBootWrap();
        bootstrap
                .selectAll(SysUser.class)
                .from(SysUser.class)
                .eq(SysUser::getDelFlag, 0)
                .in(SysUser::getId, i -> i
                        .select(SysUserDepart::getUserId)
                        .from(SysUserDepart.class)
                        .in(SysUserDepart::getDepId, departIds)
                );
        if (StringUtil.isNotBlank(username)) {
            bootstrap.eq(SysUser::getUsername, username);
        }

        IPage<SysUser> result = bootstrap.queryPage(page, SysUser.class);
        return result;
    }

    /**
     * 根据角色Id查询用户信息
     *
     * @param page
     * @param roleId
     * @param username
     * @return
     */
    @Override
    public IPage<SysUser> getUserByRoleId(Page page, String roleId, String username) {
        //        select * from sys_user where del_flag = 0 and id in (select user_id from sys_user_role where role_id=#{roleId})
        //		<if test="username!=null and username!=''">
        //                and username = #{username}
        //		</if>
        QueryLambdaBootWrap bootstrap = new QueryLambdaBootWrap();
        bootstrap
                .selectAll(SysUser.class)
                .from(SysUser.class)
                .eq(SysUser::getDelFlag, 0)
                .in(SysUser::getId, i -> i
                        .select(SysUserRole::getUserId)
                        .from(SysUserRole.class)
                        .eq(SysUserRole::getRoleId, roleId)
                );
        if (StringUtil.isNotBlank(username)) {
            bootstrap.eq(SysUser::getUsername, username);
        }

        IPage<SysUser> result = bootstrap.queryPage(page, SysUser.class);
        return result;
    }

    /**
     * 根据用户名设置部门ID
     *
     * @param username
     * @param orgCode
     */
    @Override
    public void updateUserDepart(String username, String orgCode) {
        // UPDATE sys_user SET org_code = #{orgCode} where username = #{username}
        SysUser user = new SysUser();
        user.setOrgCode(orgCode);
        EntityWrap<SysUser> wrapper = new EntityWrap<>(SysUser.class);
        wrapper.lambda().eq(SysUser::getUsername, username);
        DaoUtil.BASE.update(user, wrapper);
    }

    /**
     * 根据手机号查询用户信息
     *
     * @param phone
     * @return
     */
    @Override
    public SysUser getUserByPhone(String phone) {
        // select * from  sys_user  where phone = #{phone} and del_flag = 0
        EntityWrap<SysUser> wrapper = new EntityWrap<>(SysUser.class);
        wrapper.lambda()
                .eq(SysUser::getPhone, phone)
                .eq(SysUser::getDelFlag, 0);
        return DaoUtil.BASE.get(wrapper);
    }

    /**
     * 根据邮箱查询用户信息
     *
     * @param email
     * @return
     */
    @Override
    public SysUser getUserByEmail(String email) {
        // select * from  sys_user  where email = #{email} and del_flag = 0
        EntityWrap<SysUser> wrapper = new EntityWrap<>(SysUser.class);
        wrapper.lambda()
                .eq(SysUser::getEmail, email)
                .eq(SysUser::getDelFlag, 0);
        return DaoUtil.BASE.get(wrapper);
    }

    /**
     * 根据 orgCode 查询用户，包括子部门下的用户
     *
     * @param page       分页对象, xml中可以从里面进行取值,传递参数 Page 即自动分页,必须放在第一位(你可以继承Page实现自己的分页对象)
     * @param orgCode
     * @param userParams 用户查询条件，可为空
     * @return
     */
    @Override
    public List<SysUserSysDepartVO> getUserByOrgCode(IPage page, String orgCode, SysUser userParams) {
        QueryLambdaBootWrap bootstrap = new QueryLambdaBootWrap();
        bootstrap
                .select(SysUser::getId)
                .select(SysUser::getRealname)
                .select(SysUser::getWorkNo)
                .select(SysUser::getPost)
                .select(SysUser::getTelephone)
                .select(SysUser::getEmail)
                .select(SysUser::getPhone)
                .selectAs(SysDepart::getId, SysUserSysDepartVO::getDepartId)
                .selectAs(SysDepart::getDepartName, SysUserSysDepartVO::getDepartName)
                .from(SysDepart.class)
                .innerJoin(SysUserDepart.class, SysUserDepart::getDepId, SysDepart::getId)
                .innerJoin(SysUser.class, SysUser::getId, SysUserDepart::getUserId)
                .eq(SysUser::getDelFlag, 0)
                .like(SysDepart::getOrgCode, orgCode)
                .orderByAsc(SysDepart::getOrgCode);

        if (userParams != null) {
            if (StringUtil.isNotBlank(userParams.getRealname())) {
                bootstrap.like(SysUser::getRealname, userParams.getRealname());
            }
            if (StringUtil.isNotBlank(userParams.getWorkNo())) {
                bootstrap.like(SysUser::getWorkNo, userParams.getWorkNo());
            }
        }

        Page<SysUserSysDepartVO> resultPage = bootstrap.queryPage(page, SysUserSysDepartVO.class);
        List<SysUserSysDepartVO> list = resultPage.getRecords();
        return list;

        //        SELECT
        //        sys_user.id AS id,
        //                sys_user.realname AS realname,
        //        sys_user.work_no AS workNo,
        //                sys_user.post AS post,
        //        sys_user.telephone AS telephone,
        //                sys_user.email AS email,
        //        sys_user.phone AS phone,
        //                sys_depart.id AS departId,
        //        sys_depart.depart_name AS departName
        //        FROM
        //        sys_depart
        //        INNER JOIN sys_user_depart ON sys_user_depart.dep_id = sys_depart.id
        //        INNER JOIN sys_user ON sys_user.id = sys_user_depart.user_id
        //        WHERE
        //        sys_user.del_flag = 0 AND sys_depart.org_code LIKE '${orgCode}%'
        //
        //                <if test="userParams != null">
        //			<if test="userParams.realname != null and userParams.realname != ''">
        //                AND sys_user.realname LIKE concat(concat('%',#{userParams.realname}),'%')
        //			</if>
        //			<if test="userParams.workNo != null and userParams.workNo != ''">
        //                AND sys_user.work_no LIKE concat(concat('%',#{userParams.workNo}),'%')
        //			</if>
        //		</if>
        //        ORDER BY
        //        sys_depart.org_code ASC
    }

    /**
     * 查询 getUserByOrgCode 的Total
     *
     * @param orgCode
     * @param userParams 用户查询条件，可为空
     * @return
     */
    @Override
    public Integer getUserByOrgCodeTotal(String orgCode, SysUser userParams) {
        QueryLambdaBootWrap bootstrap = new QueryLambdaBootWrap();
        bootstrap
                .select("count(1)")
                .from(SysDepart.class)
                .innerJoin(SysUserDepart.class, SysUserDepart::getDepId, SysDepart::getId)
                .innerJoin(SysUser.class, SysUser::getId, SysUserDepart::getUserId)
                .eq(SysUser::getDelFlag, 0)
                .like(SysDepart::getOrgCode, orgCode)
                .orderByAsc(SysDepart::getOrgCode);

        if (userParams != null) {
            if (StringUtil.isNotBlank(userParams.getRealname())) {
                bootstrap.like(SysUser::getRealname, userParams.getRealname());
            }
            if (StringUtil.isNotBlank(userParams.getWorkNo())) {
                bootstrap.like(SysUser::getWorkNo, userParams.getWorkNo());
            }
        }

        Integer count = bootstrap.queryCount();
        return count;

        //        SELECT COUNT(1)
        //        FROM sys_depart
        //        INNER JOIN sys_user_depart ON sys_user_depart.dep_id = sys_depart.id
        //        INNER JOIN sys_user ON sys_user.id = sys_user_depart.user_id
        //        WHERE
        //        sys_user.del_flag = 0 AND sys_depart.org_code LIKE '${orgCode}%'
        //
        //                <if test="userParams != null">
        //			<if test="userParams.realname != null and userParams.realname != ''">
        //                AND sys_user.realname LIKE concat(concat('%',#{userParams.realname}),'%')
        //			</if>
        //			<if test="userParams.workNo != null and userParams.workNo != ''">
        //                AND sys_user.work_no LIKE concat(concat('%',#{userParams.workNo}),'%')
        //			</if>
        //		</if>

    }

    /**
     * @param roleIdArray
     * @Author scott
     * @Date 2019/12/13 16:10
     * @Description: 批量删除角色与用户关系
     */
    @Override
    public void deleteBathRoleUserRelation(String[] roleIdArray) {
        //        delete from sys_user_role
        //        where role_id in
        //                <foreach item="id" collection="roleIdArray" open="(" separator="," close=")">
        //			#{id}
        //		</foreach>
        EntityWrap<SysUserRole> wrapper = new EntityWrap<>(SysUserRole.class);
        wrapper.lambda().in(SysUserRole::getRoleId, Arrays.asList(roleIdArray));
        DaoUtil.BASE.delete(wrapper);
    }

    /**
     * @param roleIdArray
     * @Author scott
     * @Date 2019/12/13 16:10
     * @Description: 批量删除角色与权限关系
     */
    @Override
    public void deleteBathRolePermissionRelation(String[] roleIdArray) {
        //        delete from sys_role_permission
        //        where role_id in
        //                <foreach item="id" collection="roleIdArray" open="(" separator="," close=")">
        //			#{id}
        //		</foreach>
        EntityWrap<SysRoleMenu> wrapper = new EntityWrap<>(SysRoleMenu.class);
        wrapper.lambda().in(SysRoleMenu::getRoleId, Arrays.asList(roleIdArray));
        DaoUtil.BASE.delete(wrapper);
    }

    /**
     * 查询被逻辑删除的用户
     *
     * @param wrapper
     */
    @Override
    public List<SysUser> selectLogicDeleted(IWrap wrapper) {
        // SELECT * FROM sys_user ${ew.customSqlSegment}

        QueryBootWrap queryWrap = new QueryBootWrap();
        queryWrap
                .select("*")
                .from("sys_user")
                .connect(wrapper.getWhere());


        return queryWrap.queryList(SysUser.class);
    }

    /**
     * 还原被逻辑删除的用户
     *
     * @param userIds
     * @param entity
     */
    @Override
    public int revertLogicDeleted(String userIds, SysUser entity) {
        List<String> userList = Arrays.asList(StringUtil.split(userIds, StringPool.COMMA));
        SysUser user = new SysUser();
        user.setDelFlag(0);
        user.setUpdateBy(entity.getUpdateBy());
        user.setUpdateTime(entity.getUpdateTime());
        EntityWrap<SysUser> wrapper = new EntityWrap<>(SysUser.class);
        wrapper.lambda().eq(SysUser::getDelFlag, 1);
        wrapper.lambda().in(SysUser::getId, userList);

        DaoUtil.BASE.update(user, wrapper);
        return 1;

        //        UPDATE
        //                sys_user
        //        SET
        //                del_flag = 0,
        //        update_by = #{entity.updateBy},
        //        update_time = #{entity.updateTime}
        //        WHERE
        //                del_flag = 1
        //        AND id IN (${userIds})
    }

    /**
     * 彻底删除被逻辑删除的用户
     *
     * @param userIds
     */
    @Override
    public int deleteLogicDeleted(String userIds) {
        List<String> userList = Arrays.asList(StringUtil.split(userIds, StringPool.COMMA));
        EntityWrap<SysUser> wrapper = new EntityWrap<>(SysUser.class);
        wrapper.lambda().eq(SysUser::getDelFlag, 1);
        wrapper.lambda().in(SysUser::getId, userList);
        DaoUtil.BASE.realDel(wrapper);
        return 1;

        // DELETE FROM sys_user WHERE del_flag = 1 AND id IN (${userIds})
    }

    /**
     * 更新空字符串为null【此写法有sql注入风险，禁止随便用】
     *
     * @param fieldName
     */
    @Override
    public int updateNullByEmptyString(String fieldName) {
        // UPDATE sys_user SET ${fieldName} = NULL WHERE ${fieldName} = ''
        String sql = "UPDATE sys_user SET " + fieldName + " = NULL WHERE " + fieldName + " = ''";
        JdbcDaoUtil.exec(sql, DbUtil.datasource);
        return 0;
    }

    /**
     * 根据部门Ids,查询部门下用户信息
     *
     * @param departIds
     * @param username
     * @return
     */
    @Override
    public List<SysUser> queryByDepIds(List<String> departIds, String username) {
        //        select * from sys_user where del_flag = 0
        //                <if test="departIds!=null  and departIds.size()>0">
        //                and id in (select user_id from sys_user_depart where dep_id in
        //			<foreach collection="departIds" index="index" item="id" open="(" separator="," close=")">
        //				#{id}
        //			</foreach>
        //			)
        //		</if>
        //		<if test="username!=null and username!=''">
        //                and username != #{username}
        //		</if>

        QueryLambdaBootWrap lambdaWrap = new QueryLambdaBootWrap();
        lambdaWrap
                .selectAll(SysUser.class)
                .from(SysUser.class)
                .eq(SysUser::getDelFlag, 0);

        if (departIds != null && departIds.size() > 0) {
            lambdaWrap.in(SysUser::getId, i -> i
                    .select(SysUserDepart::getUserId)
                    .from(SysUserDepart.class)
                    .in(SysUserDepart::getDepId, departIds)
            );
        }

        if (StringUtil.isNotBlank(username)) {
            lambdaWrap.eq(SysUser::getUsername, username);
        }

        List<SysUser> list = lambdaWrap.queryList(SysUser.class);
        return list;
    }
}
